package indi.zn.commons.simplepoi;



import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class SSFBeanColumMatch<T> extends SSFBeanAbstract<T> {
	
	
	public SSFBeanColumMatch(Class<T> clazz, SSFType type) {
		super(clazz,type);
		

	}

	public SSFBeanColumMatch(Class<T> clazz,SSFType type, Integer startrow,
			Integer endrow, String[] ColumNumMatchFieldNames) {
	  super(clazz,type);
		this.MatchFieldNames = ColumNumMatchFieldNames;
		this.end = endrow;
		this.start = startrow;

	}

	public Boolean getFormula() {
				
		return formula;
	}

	public void setFormula(Boolean formula) {
		this.formula = formula;
	}

	public void setMatchFieldNames(String[] match) {

		this.MatchFieldNames = match;
	}

	public void setStatEndNum(Integer start, Integer end) {
		this.start = start;
		this.end = end;
	}

	@SuppressWarnings("rawtypes")
	public List<T> xls2bean(Workbook workbook, String sheetname) throws Exception {
		List<T> beanList = new ArrayList<T>();

	   
		Sheet sheet = workbook.getSheet(sheetname);
		
		if(sheet==null){
			
			workbook.getSheetAt(1);
		
		}
		
		if (hfe == null) {
			
	if(type.equals(SSFType.HSSF)) hfe = new  HSSFFormulaEvaluator((HSSFWorkbook)workbook);  
			
	if(type.equals(SSFType.XSSF)) hfe = new  XSSFFormulaEvaluator((XSSFWorkbook)workbook);
			
		}
		
		for (int i = start; i < end; i++) {
			Row row = sheet.getRow(i);

			if (row == null) {
				continue;
			}

			T obj = null;

				obj = clazz.newInstance();
				for (int j = 0; j < MatchFieldNames.length; j++) {

					if (MatchFieldNames[j] == null
							|| MatchFieldNames[j].equals("")) {
						continue;
					}

					Cell cell = row.getCell(j);

					Field f;

					f = clazz.getDeclaredField(MatchFieldNames[j]);

					Class type = f.getType();

					String getMethodname = "set"
							+ MatchFieldNames[j].substring(0, 1).toUpperCase()
							+ MatchFieldNames[j].substring(1);

					Method method = clazz.getMethod(getMethodname, type);

				

					Object value = getValue(cell, hfe, formula);

					if (value == null) {
						value = "";
					}

					if (Integer.class.equals(type)) {
						method.invoke(obj, Integer.parseInt(value.toString()));
					}

					if (String.class.equals(type)) {
						method.invoke(obj, value.toString());
					}

					if (Double.class.equals(type)) {
						
						method.invoke(obj,Double.parseDouble(value.toString()));
						
					}

				}

			beanList.add(obj);

		}
		return beanList;

	}
	
	
	
	
	
	

	public Workbook bean2xls(Workbook workbook, String sheetname,
			List<T> list) throws Exception {
		
		
		Sheet sheet= workbook.getSheet(sheetname);	
		   if(sheet==null) sheet= workbook.createSheet(sheetname);
		   
	
		
		if (hfe == null) {
	if(type.equals(SSFType.HSSF)) hfe = new  HSSFFormulaEvaluator((HSSFWorkbook)workbook);  
	if(type.equals(SSFType.XSSF)) hfe = new  XSSFFormulaEvaluator((XSSFWorkbook)workbook);
		}
		
		@SuppressWarnings("unused")
		List<Integer> allMergedRegions=null; 
		
		if(lastLines!=null&&lastLines[0]<start+list.size()){
			workbook.getSheet(sheetname).shiftRows(lastLines[0], lastLines[lastLines.length-1], moveLines);
		}
		
		//Row row_first = sheet.getRow(start);
		
		for (int i = 0; i < end-start; i++) {
			Row row = sheet.getRow(i+start);
			
			if (row == null) {
				row=sheet.createRow(i+start);
				
				
			 if(ws.getRowHight(i+start)!=null){
				row.setHeight(ws.getRowHight(i+start));	
			 }
			 
				
			}

			T obj = list.get(i);

				for (int j = 0; j < MatchFieldNames.length; j++) {

					if(i==1){
						
						 if(getWs().getColumHight(i)!=null){
							 sheet.setColumnWidth(j, getWs().getColumHight(i));	
						   }
						}
					
					
					if (MatchFieldNames[j] == null
							|| MatchFieldNames[j].equals("")) {
						continue;
					}
					Cell cell = row.getCell(j);
					
					if(cell==null){
						cell=row.createCell(j);
					}
					
					
					setCellStyle(i+start, j, cell,workbook);
					
				  
					//cell.setCellStyle(row_first.getCell(j).getCellStyle());

					String getMethodname = "get"
							+ MatchFieldNames[j].substring(0, 1).toUpperCase()
							+ MatchFieldNames[j].substring(1);
					Method method = clazz.getMethod(getMethodname);
					Object value =	method.invoke(obj);
					if (value != null) {				
						
					setValue(cell, value, formula);
					
						
					}
				}
		}
		
		return workbook;
	}
	
	/**
	 * 
	 * setFormula:
	 * 
	 * @author 张大伟
	 * @param sheet
	 * @param formulaArray
	 * @param line
	 * @since 　Ver 1.1
	 */
	public void setFormula(Sheet sheet,String[] formulaArray,Integer line){
		Row row = sheet.getRow(line);
		
		for (int j = 0; j <formulaArray.length; j++) {
			Cell cell=row.getCell(j);	
			if(formulaArray[j] != "合计" && !"".equals(formulaArray[j])){
				cell.setCellFormula(formulaArray[j]);
				cell.setCellType(HSSFCell.CELL_TYPE_FORMULA); 
			}else{
			
			if(cell!=null){	
			cell.setCellValue(formulaArray[j]);
			}
			}
		}
	}
	public void setFormulaRow(Sheet sheet,Integer cellNum,Integer star,Integer end){
		Row r = sheet.getRow(star);
		Cell c  = r.getCell(cellNum);
		String formula = "";
		if(c.getCellType() == HSSFCell.CELL_TYPE_FORMULA){
			formula = c.getCellFormula();
		}
		for(int i = star ; i < end ; i++){
			String f = formula.replace((star+1)+"", (i+1)+"");
			Row row = sheet.getRow(i);
			if(!"".equals(f)){
				for (int j = 0; j <row.getLastCellNum(); j++) {
					if(j == cellNum){
						Cell cell=row.getCell(j);	
						cell.setCellFormula(f);
						cell.setCellType(HSSFCell.CELL_TYPE_FORMULA); 
					}
				}
			}
		}
	}

}
